Watsonx.data Presto
Querying Overview
Qarbine utilizes the IBM Presto REST interface to access data within the Watsonx.data Presto engine environment. For querying the primary language is SQL. A Presto SQL statement reference is at
https://www.ibm.com/docs/en/watsonx/watsonxdata/2.0.x?topic=presto-sql-statements
and
There are additional virtual query statements described below.
Sample Analysis
Qarbine provides a robust set of features for flexible analysis. Here is an example that uses Qarbine Prompts, Data Sources, and Templates. The template is located at “example/IBM/Watson/Presto/Order details for prompted retailer”.
Running the template presents a prompt to the user with a list.
Choose a retailer proceed.
Click
The retailer name is passed along to the data source’s query
select a.*, b.*,c.product_name
from order_header a, order_details b, product_name_lookup c
where retailer_name = @retailer and
a.order_method_code = 6 and
a.order_number = b.order_number and
b.product_number = c.product_number AND
c.product_language = 'EN'
order by a.order_number
limit 30
The answer set is formatted based on the template. The results are shown below.
Qarbine and Presto Semantics
The Qarbine object to Presto object mapping is enumerated below.
| Qarbine Object | Presto Object |
|---|---|
| catalog | |
| database | schema |
| collection or table | table |
The Qarbine administrator can configure a Qarbine data service in several manners.
| Data Service Configuration | Affect of Listed “Databases” in the Query Tools |
|---|---|
| No specific catalog and database | The list contains a list based on all of the catalog names and their contained schema.The format of the list elements is “catalog.schema”. |
| A specific catalog | The list contains the names of the Presto schema within the specified catalog. |
| A specific catalog and database. | The list has a single entry of the specified database (AKA Presto schema). |
Presto Data Handling
Qarbine automatically performs data conversions based on the Presto schema for these data types:
- decimal,
- date and
- date only.
Note - The schema information is cached for the duration on the Qarbine host process which is interacting with the IBM Presto endpoint. If the Presto table schema changes then restart the appropriate Qarbine hosts. There is no programmatic option to be made aware of such schema changes that could selectively flush cache entries.
Qarbine Virtual Queries
There are a few convenience queries which are mainly DBA oriented. These queries are recognized by the Qarbine driver and provide common database information. Any catalog and schema set in the data service definition constrains what is returned. For example, if a catalog is given in the data service, then only schemas in that one catalog are returned.
These virtual query defaults are independent of whatever drop down option is chosen in the Data Source Designer tool. If a specific schema’s information is wanted for example, it must be explicitly given.
| Query | Description |
|---|---|
| list catalogs | Return a list of catalogs. |
| list schemas [CATALOG] | Return a list of schemas. Optionally provide a catalog name when there is none in the data service. |
| describe schema [SCHEMA]describe schema [CATALOG.SCHEMA] | Return the details of the given schema. The optional argument may be of the form “schema” or “catalog.schema”. |
| list tables [SCHEMA]list tables [CATALOG.SCHEMA] | Return a list of tables. The optional argument may be of the form “schema” or “catalog.schema”. |
| describe tables [SCHEMA]describe tables [CATALOG.SCHEMA] | Provide details on all of the tables. The optional argument may be of the form “schema” or “catalog.schema”. This may take a while depending on your database structure. |
| describe table TABLEdescribe table [SCHEMA.TABLE]describe table [CATALOG.SCHEMA.TABLE] | Provide details on the given table. The argument may be of the form “catalog.schema.table”, “schema.table” or “table”. |
See the “DBA Productivity” section of the online documentation for more details.
Troubleshooting
If the query used within Qarbine is not yielding the anticipated results, then the Watson Query workspace can be used to test queries.
Be sure to use the Presto engine as shown below when running your query.
For general information on running queries see https://cloud.ibm.com/docs/watsonxdata?topic=watsonxdata-run_sql
A Presto SQL statement reference is at
https://www.ibm.com/docs/en/watsonx/watsonxdata/2.0.x?topic=presto-sql-statements
and
Presto Specific Custom Prompt
A Qarbine custom prompt is defined which is aware of IBM Watsonx.data Presto catalog and their contained schema. The Prompt is at “example/IBM/Watson/Presto/Prompt for catalog and schema”. It can easily be reused by your own data sources and templates.
Running the prompt first displays a list of catalogs to choose from.
Selecting the sample_data catalog shows
The user can then select one of the schemas.
Click
to set the catalog and schema variables which may be used in data sources and templates.